import Database from 'better-sqlite3';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const db = new Database(join(__dirname, 'geometry.db'));

// 创建表结构
db.exec(`
  -- 管理员用户表
  CREATE TABLE IF NOT EXISTS admin_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,  -- SHA-256哈希存储
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  -- 会话表
  CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT UNIQUE NOT NULL,
    username TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (username) REFERENCES admin_users(username) ON DELETE CASCADE
  );

  -- 验证码表
  CREATE TABLE IF NOT EXISTS captchas (
    id TEXT PRIMARY KEY,
    answer INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL
  );

  -- 章节表
  CREATE TABLE IF NOT EXISTS chapters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    order_num INTEGER NOT NULL,
    is_hidden BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  -- 几何模型表
  CREATE TABLE IF NOT EXISTS geometry_models (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chapter_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    canvas_data TEXT NOT NULL,  -- JSON格式存储canvas设计数据（母版数据）
    order_num INTEGER NOT NULL,
    is_special BOOLEAN DEFAULT 0,  -- 是否是特殊模型
    template_path TEXT,  -- 特殊模型的模板路径（如: 'TriangleHeight'）
    is_hidden BOOLEAN DEFAULT 0,  -- 是否隐藏（用户端不显示）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE
  );

  -- 模型状态表（每个模型可以有多个状态/按钮）
  CREATE TABLE IF NOT EXISTS model_states (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_id INTEGER NOT NULL,
    button_label TEXT NOT NULL,  -- 按钮显示文本
    canvas_data TEXT NOT NULL,  -- JSON格式存储该状态的canvas数据（基于母版修改）
    order_num INTEGER NOT NULL,  -- 按钮显示顺序
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (model_id) REFERENCES geometry_models(id) ON DELETE CASCADE
  );

  -- 全局设置表（存储通用配置）
  CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT UNIQUE NOT NULL,  -- 设置键名
    setting_value TEXT NOT NULL,  -- 设置值（JSON格式）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  -- 创建索引
  CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
  CREATE INDEX IF NOT EXISTS idx_captchas_expires ON captchas(expires_at);
  CREATE INDEX IF NOT EXISTS idx_chapters_order ON chapters(order_num);
  CREATE INDEX IF NOT EXISTS idx_models_chapter ON geometry_models(chapter_id);
  CREATE INDEX IF NOT EXISTS idx_models_order ON geometry_models(order_num);
  CREATE INDEX IF NOT EXISTS idx_states_model ON model_states(model_id);
  CREATE INDEX IF NOT EXISTS idx_states_order ON model_states(order_num);
`);

// 插入默认管理员账号（如果不存在）
import crypto from 'crypto';

const adminCount = db.prepare('SELECT COUNT(*) as count FROM admin_users').get();
if (adminCount.count === 0) {
  const defaultPassword = crypto.createHash('sha256').update('admin123').digest('hex');
  db.prepare(`
    INSERT INTO admin_users (username, password) 
    VALUES (?, ?)
  `).run('admin', defaultPassword);
  
  console.log('✓ 已创建默认管理员账号: admin / admin123');
}

// 清理过期的会话和验证码
db.prepare("DELETE FROM sessions WHERE expires_at < datetime('now')").run();
db.prepare("DELETE FROM captchas WHERE expires_at < datetime('now')").run();

// 插入默认设置（如果不存在）
const settingsCount = db.prepare('SELECT COUNT(*) as count FROM settings').get();
if (settingsCount.count === 0) {
  const defaultSettings = {
    commonColors: ['#FF0000', '#00FF00', '#0000FF'],  // 常用色1、2、3
    shadowColor: 'rgba(128, 128, 128, 0.15)',         // 固定阴影色
    defaultFontSizes: {
      lineLabel: 16,             // 线段标签默认字号
      areaLabel: 18,             // 面积标签默认字号
      pointLabel: 16             // 点标签默认字号
    }
  };

  db.prepare(`
    INSERT INTO settings (setting_key, setting_value)
    VALUES (?, ?)
  `).run('workspace_defaults', JSON.stringify(defaultSettings));

  console.log('✓ 已创建默认设置');
}

// 插入示例数据（如果表为空）
const chapterCount = db.prepare('SELECT COUNT(*) as count FROM chapters').get();
if (chapterCount.count === 0) {
  const insertChapter = db.prepare(`
    INSERT INTO chapters (title, order_num, is_hidden) 
    VALUES (?, ?, ?)
  `);
  
  const chapter1 = insertChapter.run('三角形的基础', 1, 0);
  const chapter2 = insertChapter.run('三角形的高', 2, 0);
  
  // 插入示例几何模型
  const insertModel = db.prepare(`
    INSERT INTO geometry_models (chapter_id, title, canvas_data, order_num, is_special, template_path, is_hidden)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `);
  
  // 创建一个三角形模型（使用新的通用数据格式）
  const defaultCanvasData = JSON.stringify({
    points: [
      { id: 'point_A', x: 350, y: 150, label: 'A', color: '#007AFF', draggable: true },
      { id: 'point_B', x: 100, y: 400, label: 'B', color: '#007AFF', draggable: true },
      { id: 'point_C', x: 650, y: 400, label: 'C', color: '#007AFF', draggable: true }
    ],
    lines: [
      { id: 'line_AB', startPointId: 'point_A', endPointId: 'point_B', style: 'solid', color: '#333', width: 2 },
      { id: 'line_BC', startPointId: 'point_B', endPointId: 'point_C', style: 'solid', color: '#333', width: 2 },
      { id: 'line_CA', startPointId: 'point_C', endPointId: 'point_A', style: 'solid', color: '#333', width: 2 }
    ],
    labels: [
      { id: 'label_title', x: 320, y: 250, text: '拖动顶点改变三角形形状', fontSize: 16, color: '#666' }
    ],
    constants: [],  // 新增常量字段
    config: {
      pointSize: 8,
      lineWidth: 2
    }
  });
  
  // 普通几何模型（使用通用编辑器）
  insertModel.run(chapter2.lastInsertRowid, '基础三角形', defaultCanvasData, 1, 0, null, 0);
  
  // 创建一个四边形示例
  const quadrilateralData = JSON.stringify({
    points: [
      { id: 'point_A', x: 250, y: 150, label: 'A', color: '#007AFF', draggable: true },
      { id: 'point_B', x: 550, y: 150, label: 'B', color: '#007AFF', draggable: true },
      { id: 'point_C', x: 600, y: 400, label: 'C', color: '#007AFF', draggable: true },
      { id: 'point_D', x: 200, y: 400, label: 'D', color: '#007AFF', draggable: true }
    ],
    lines: [
      { id: 'line_1', startPointId: 'point_A', endPointId: 'point_B', style: 'solid', color: '#333', width: 2 },
      { id: 'line_2', startPointId: 'point_B', endPointId: 'point_C', style: 'solid', color: '#333', width: 2 },
      { id: 'line_3', startPointId: 'point_C', endPointId: 'point_D', style: 'solid', color: '#333', width: 2 },
      { id: 'line_4', startPointId: 'point_D', endPointId: 'point_A', style: 'solid', color: '#333', width: 2 },
      { id: 'line_diagonal', startPointId: 'point_A', endPointId: 'point_C', style: 'dashed', color: '#999', width: 1.5 }
    ],
    labels: [
      { id: 'label_1', x: 380, y: 250, text: '对角线', fontSize: 14, color: '#666' }
    ],
    constants: [],  // 新增常量字段
    config: {
      pointSize: 8,
      lineWidth: 2
    }
  });
  
  insertModel.run(chapter1.lastInsertRowid, '四边形与对角线', quadrilateralData, 1, 0, null, 0);
  
  // 特殊模型示例：三角形高线（使用自定义模板）
  const triangleHeightData = JSON.stringify({
    type: 'triangle-height',
    points: {
      A: { x: 350, y: 150 },
      B: { x: 100, y: 400 },
      C: { x: 650, y: 400 }
    },
    config: {
      showAngles: true,
      showHeights: true,
      showLabels: true,
      fillColor: 'rgba(255, 220, 150, 0.8)',
      strokeColor: '#000',
      heightColor: 'rgba(255, 140, 0, 0.8)'
    }
  });
  
  insertModel.run(chapter2.lastInsertRowid, '三角形的高（特殊模型）', triangleHeightData, 2, 1, 'TriangleHeight', 0);
  
  console.log('✓ 已插入示例数据');
}

// 定期清理过期数据（每小时）
setInterval(() => {
  db.prepare("DELETE FROM sessions WHERE expires_at < datetime('now')").run();
  db.prepare("DELETE FROM captchas WHERE expires_at < datetime('now')").run();
  console.log('✓ 已清理过期的会话和验证码');
}, 60 * 60 * 1000);

export default db;

